package bounjour;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Vector;

/**
 * 
 * Description: This class is the general manager class for accessing MySQL DB
 * 
 * @author: Senem Beken
 * @date: 07.05.2011
 */

public class RecordManager 
{
	private static String DRIVER = "com.mysql.jdbc.Driver";
	private static String URL = "jdbc:mysql://vault103.com:3306/rasputin_bounjour";
	private static String username = "rasputin_bounadm";
	private static String password = "b0unj0ur$";
	private static String database = "rasputin_bounjour";

	private Connection connection;
	private Statement statement = null;

	public static final RecordManager rmInstance = new RecordManager();
	
	public static RecordManager getRecordManager()
	{
		return rmInstance;
	}
	
	// constructor #1
	@SuppressWarnings("static-access")
	private RecordManager(String driver, String url, String username, String password) 
	{
		this.DRIVER = driver;
		this.URL = url;
		try 
		{
			openConnection(driver, url, username, password);
		} 
		catch (java.lang.Exception e) 
		{
			System.out.println("Error connecting to database");
		}
	}

	// constructor #2 (our actual constructor for now)
	private RecordManager() 
	{
		this(DRIVER, URL, username, password);
	}

	/* close DB connection */
	public void closeConnection() 
	{
		try 
		{
			if (connection != null)
				connection.close();
		} 
		catch (Exception e) 
		{
			System.err.println("Connection could not be closed:" + e);
		}
	}

	/*
	 * connection done to DB is established by URL and driver information the
	 * connection this function is called by constructor
	 */
	public void openConnection(String driver, String url, String username,
							   String password) throws Exception 
	{
		Class.forName(DRIVER);
		connection = DriverManager.getConnection(URL, username, password);
		statement = connection.createStatement();
		statement.execute("use " + database + ";");
	}

	/* for inserting multiple columns to a table */
	public int insertRecord(String tableName, ArrayList<String> columnList, 
							ArrayList<String> valueList) throws SQLException 
	{
		// parse values into values string
		String valueString = "";
		int valuesLength = valueList.size();
		for (int j = 0; j < valuesLength; j++) 
		{
			valueString += Util.appendColon(valueList.get(j));

			if (j < (valuesLength - 1)) // place "," between parameter list
				valueString += ", ";
		}

		// parse column list into columns string
		String columnString = "";
		int columnsLength = columnList.size();
		for (int j = 0; j < columnsLength; j++) 
		{
			columnString += columnList.get(j);
			if (j < (columnsLength - 1)) // place "," between parameter list
				columnString += ", ";
		}

		return insertRecord(tableName, columnString, valueString);
	}

	/* for inserting a single column to a table */
	public int insertRecord(String tableName, String columnString, String valueString) 
							throws SQLException
	{
		valueString  = Util.appendColon(valueString);
			
		String sqlQuery = "INSERT INTO " + tableName + " (" + columnString
						  + ") VALUES (" + valueString + ")";
		
		return statement.executeUpdate(sqlQuery);
	}

	/* for selecting multiple columns without any search criteria */
	public Vector <HashMap<String, Object>>selectRecord(ArrayList<String> columnList, String tableName) 
							  throws SQLException 
	{
		// parse column list into columns string
		String columnString = "";
		int columnsLength = columnList.size();
		for (int j = 0; j < columnsLength; j++) 
		{
			columnString += columnList.get(j);

			if (j < (columnsLength - 1)) // place "," between parameter list
				columnString += ", ";
		}

		return selectRecord(columnString, tableName);
	}

	/* for selecting a single column without any search criteria */
	public Vector <HashMap<String, Object>>selectRecord(String columnString, String tableName) throws SQLException 
	{
		//columnString = Util.AppendColon(columnString);
		String sqlQuery = " SELECT " + columnString + " from " + tableName;
		return getRecordVector(sqlQuery);
	}

	/* for selecting multiple columns with the given search criteria */
	public Vector <HashMap<String, Object>>selectRecord(ArrayList<String> columnList, String tableName,
							   String columnName, String columnValue) throws SQLException 
	{
		// parse column list into columns string
		String columnString = "";
		int columnsLength = columnList.size();
		for (int j = 0; j < columnsLength; j++) 
		{
			columnString += columnList.get(j);

			if (j < (columnsLength - 1)) // place "," between parameter list
				columnString += ", ";
		}

		return selectRecord(columnString, tableName, columnName,
				columnValue);
	}
	/* for selecting multiple colums with the given search criterias*/
	public Vector <HashMap<String, Object>>selectRecord(ArrayList<String> columnList, String tableName,
			   ArrayList<String> columnNameList, ArrayList<String> columnValueList) throws SQLException 
{
	String columnString = "";
	String whereString = "";
	int columnsLength = columnList.size();
	
	for (int j = 0; j < columnsLength; j++) 
	{
		columnString += columnList.get(j);

		if (j < (columnsLength - 1)) // place "," between parameter list
			columnString += ", ";
	}
	int columnNamesLength = columnNameList.size();
	for (int j = 0; j < columnNamesLength; j++) {
		whereString += columnNameList.get(j) + " = '" + columnValueList.get(j) + "'";
		if (j < (columnNamesLength - 1)) // place "," between parameter list
			whereString += " AND ";
	}
	String sqlQuery = " SELECT " + columnString + " from " + tableName + " WHERE " + whereString;
	return getRecordVector(sqlQuery);
	
}	
	
	/* for selecting one columns with the given search criteria */
	public Vector <HashMap<String, Object>>selectRecord(String columnString, String tableName,
							   String columnName, String columnValue) throws SQLException 
	{
		columnValue = Util.appendColon(columnValue);
		
		String sqlQuery = " SELECT " + columnString + " from " + tableName + " WHERE " + columnName + " = " + columnValue;
		return getRecordVector(sqlQuery);
	}

	/* for generic select queries */
	public Vector <HashMap<String, Object>> selectRecord(String sqlQuery) throws SQLException 
	{
		return getRecordVector(sqlQuery);
	}

	/* for updating data of multiple columns with the given selection criteria */
	public int updateRecord(String tableName, ArrayList<String> columnList, 
							ArrayList<String> valueList, String columnName, String columnValue) 
							throws SQLException 
	{
		int numOfUpdatedColumns = 0;
		String columnValueString ="";
		
		// sizes have to match
		if(columnList.size() == valueList.size())
		{
			// parse columns and values into column-values string
			int valuesLength = valueList.size();
			
			for (int j = 0; j < valuesLength; j++) 
			{
				columnValueString += columnList.get(j) + " = " + Util.appendColon(valueList.get(j));
	
				if (j < (valuesLength - 1)) // place "," between parameter list
					columnValueString += ", ";
			}
	
			columnValue = Util.appendColon(columnValue);
			
			String sqlQuery = "UPDATE " + tableName + " SET " + columnValueString + 
							 " WHERE " + columnName + " = " + columnValue;
			
			numOfUpdatedColumns = statement.executeUpdate(sqlQuery);
		}
		
		return numOfUpdatedColumns;
	}

	/* for updating data of one column with the given selection criteria */
	public int updateRecord(String tableName, String columnString,
							String valueString, String columnName, String columnValue) 
							throws SQLException 
	{
		columnValue = Util.appendColon(columnValue);
		valueString = Util.appendColon(valueString);
		
		String sqlQuery = "UPDATE " + tableName + " SET " + columnString
				+ " = " + valueString + " WHERE " + columnName + " = " + columnValue;
		
		return statement.executeUpdate(sqlQuery);
	}

	/* for generic update queries */
	public int updateRecord(String sqlQuery) throws SQLException 
	{
		return statement.executeUpdate(sqlQuery);
	}
	
	/* delete a single row from the given table */
	public int deleteRecord(String tableName, String columnName, String columnValue) throws SQLException
	{
		columnValue = Util.appendColon(columnValue);
		String sqlQuery = "DELETE from " + tableName + "WHERE " + columnName + " = " + columnValue;
		return statement.executeUpdate(sqlQuery);
	}
	
	/* for generic delete queries */
	public int deleteRecord(String sqlQuery) throws SQLException
	{
		return statement.executeUpdate(sqlQuery);
	}

	public HashMap<String, Object> getSingleResult(String sqlQuery) throws SQLException
	{
		ResultSet valueResultSet = null;
		HashMap<String, Object>recordList = new HashMap<String, Object>();

		valueResultSet = statement.executeQuery(sqlQuery);
		ResultSetMetaData metaData = valueResultSet.getMetaData();

		int columnNum = metaData.getColumnCount();

		while (valueResultSet.next()) 
		{
			for (int i = 1; i <= columnNum; i++)
				recordList.put(metaData.getColumnName(i), valueResultSet.getObject(i));
		}
		
		if (valueResultSet != null)
			valueResultSet.close();

		return recordList;
	}
	
	private Vector <HashMap<String, Object>>getRecordVector(String sqlQuery) 
	{
		ResultSet valueResultSet = null;
		Vector <HashMap<String, Object>>recordList = new Vector<HashMap<String, Object>>();

		try 
		{
			valueResultSet = statement.executeQuery(sqlQuery);
			ResultSetMetaData metaData = valueResultSet.getMetaData();

			int columnNum = metaData.getColumnCount();

			while (valueResultSet.next()) 
			{
				HashMap<String, Object>hashmap = new HashMap<String, Object>();
				for (int i = 1; i <= columnNum; i++)
					hashmap.put(metaData.getColumnName(i), valueResultSet.getObject(i));

				recordList.add(hashmap);
			}

			if (valueResultSet != null)
				valueResultSet.close();
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}

		return recordList;
	}

	@SuppressWarnings("unused")
	private Vector <Vector<Object>> getRecordVector(int columnIndex, String sqlQuery) 
	{
		ResultSet valueResultSet = null;
		Vector <Vector<Object>>recordList = new Vector<Vector<Object>>();
		Statement statement = null;

		try 
		{
			statement = this.connection.createStatement();
			valueResultSet = statement.executeQuery(sqlQuery);
			Vector <Object>row = new Vector<Object>();

			while (valueResultSet.next())
				row.add(valueResultSet.getObject(columnIndex));

			recordList.add(row);
			
			if (valueResultSet != null)
				valueResultSet.close();

			if (statement != null)
				statement.close();
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}

		return recordList;
	}
}